﻿using ChurchDiary.Common;
using ChurchDiary.DAL.Contract;
using ChurchDiary.DAL.DAL;
using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
namespace ChurchDiary.DAL.Methods
{
    public class DAL_Website : DataAccess, IWebsite
    {
        #region Magazhine
        public string InsertMagazhine(EntityMagazine Magazhine)
        {
            string IResult = "SME";
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Magazine]"))
            {
                SQLServer.AddInParameter(dbCommand, "@MagazineHead", DbType.String, Magazhine.MagazineHead);
                SQLServer.AddInParameter(dbCommand, "@Month", DbType.String, Magazhine.MagazineMonth);
                SQLServer.AddInParameter(dbCommand, "@FileName", DbType.String, Magazhine.MagazineFile);
                SQLServer.AddInParameter(dbCommand, "@Year", DbType.String, Magazhine.MagazineYear);
                SQLServer.AddInParameter(dbCommand, "@IndexImage", DbType.String, CommonFunction.UploadImage("~/Uploads/Magazine/IndexImages", Magazhine.MagazineStartImage));
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                }
            }
            return IResult;
        }
        public string UpdateMagazhine(EntityMagazine Magazhine)
        {
            string IResult = "SME";
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Magazine]"))
            {
                SQLServer.AddInParameter(dbCommand, "@MagazineId", DbType.Guid, Magazhine.MagazineId);
                SQLServer.AddInParameter(dbCommand, "@MagazineHead", DbType.String, Magazhine.MagazineHead);
                SQLServer.AddInParameter(dbCommand, "@Month", DbType.String, Magazhine.MagazineMonth);
                SQLServer.AddInParameter(dbCommand, "@FileName", DbType.String, Magazhine.MagazineFile);
                SQLServer.AddInParameter(dbCommand, "@Year", DbType.String, Magazhine.MagazineYear);
                SQLServer.AddInParameter(dbCommand, "@IndexImage", DbType.String, CommonFunction.UploadImage("~/Uploads/Magazine/IndexImages", Magazhine.MagazineStartImage));
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                }
            }
            
            return IResult;
        }
        public string DeleteMagazhine(Guid sMagazhineid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Magazine]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@MagazineId", DbType.Guid, sMagazhineid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntityMagazine GetMagazhine(Guid sMagazhineid)
        {
            EntityMagazine magazhine = new EntityMagazine();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Magazine]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                    
                        SQLServer.AddInParameter(dbCommand, "@Magazhineid", DbType.Guid, sMagazhineid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return magazhine;
                            }
                            while (OReader.Read())
                            {
                                magazhine.MagazineId = (Guid)OReader["MagazineId"];
                                magazhine.MagazineHead = OReader["MagazineHead"].ToString();
                                magazhine.MagazineMonth = OReader["MagazineMonth"].ToString();
                                magazhine.MagazineStartImage = OReader["MagazineStartImage"].ToString();
                                magazhine.MagazineYear = OReader["MagazineYear"].ToString();
                                magazhine.MagazineFile = OReader["MagazineFile"].ToString();
                                magazhine.Status = (int)OReader["Status"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return magazhine;
        }
        public List<EntityMagazine> GetMagazhines()
        {
            List<EntityMagazine> lstmagazhine = new List<EntityMagazine>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Magazine]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return lstmagazhine;
                            }
                            while (OReader.Read())
                            {
                                EntityMagazine magazhine = new EntityMagazine();
                                magazhine.MagazineId = (Guid)OReader["MagazineId"];
                                magazhine.MagazineHead = OReader["MagazineHead"].ToString();
                                magazhine.MagazineMonth = OReader["MagazineMonth"].ToString();
                                magazhine.MagazineStartImage = OReader["MagazineStartImage"].ToString();
                                magazhine.MagazineYear = OReader["MagazineYear"].ToString();
                                magazhine.MagazineFile = OReader["MagazineFile"].ToString();
                                lstmagazhine.Add(magazhine);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return lstmagazhine;
        }
        public List<EntityMagazine> GetMagazhinesHomePage()
        {
            List<EntityMagazine> lstmagazhine = new List<EntityMagazine>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Magazine]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.SEARCH);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return lstmagazhine;
                            }
                            while (OReader.Read())
                            {
                                EntityMagazine magazhine = new EntityMagazine();
                                magazhine.MagazineId = (Guid)OReader["MagazineId"];
                                magazhine.MagazineHead = OReader["MagazineHead"].ToString();
                                magazhine.MagazineMonth = OReader["MagazineMonth"].ToString();
                                magazhine.MagazineStartImage = OReader["MagazineStartImage"].ToString();
                                magazhine.MagazineYear = OReader["MagazineYear"].ToString();
                                magazhine.MagazineFile = OReader["MagazineFile"].ToString();
                                lstmagazhine.Add(magazhine);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return lstmagazhine;
        }
        #endregion
        #region news
        public string Insertnews(EntityNews news)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_News]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@NewsCategory", DbType.Guid, news.NewsCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@NewsDate", DbType.String, news.NewsDate);
                    SQLServer.AddInParameter(dbCommand, "@NewsName", DbType.String, news.NewsName);
                    SQLServer.AddInParameter(dbCommand, "@NewsAcitive", DbType.DateTime, news.NewsActiveDate);
                    SQLServer.AddInParameter(dbCommand, "@NewsExpariy", DbType.DateTime, news.NewsExpairyDate);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateNews(EntityNews news)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_News]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, news.NewsId);
                    SQLServer.AddInParameter(dbCommand, "@NewsCategory", DbType.Guid, news.NewsCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@NewsDate", DbType.String, news.NewsDate);
                    SQLServer.AddInParameter(dbCommand, "@NewsAcitive", DbType.DateTime, news.NewsActiveDate);
                    SQLServer.AddInParameter(dbCommand, "@NewsExpariy", DbType.DateTime, news.NewsExpairyDate);
                    SQLServer.AddInParameter(dbCommand, "@NewsName", DbType.String, news.NewsName);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteNews(Guid Newsid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_News]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, Newsid);
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityNews> GetNewses()
        {
            List<EntityNews> newses = new List<EntityNews>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_News]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return newses;
                            }
                            while (OReader.Read())
                            {
                                EntityNews news = new EntityNews();
                                news.NewsId = (Guid)OReader["NewsId"];
                                news.NewsName = OReader["NewsName"].ToString();
                                //news.NewsDate = CommonFunction.ConvertDate(OReader["NewsDate"].ToString());
                                //news.NewsActiveDate = CommonFunction.ConvertDate(OReader["NewsActiveDate"].ToString());
                                //news.NewsExpairyDate = CommonFunction.ConvertDate(OReader["NewsExpairyDate"].ToString());
                                news.NewsCategory = new EntityNewsCategory();
                                news.NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                news.NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                                newses.Add(news);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return newses;
        }
        public EntityNews GetNews(Guid Newsid)
        {
            EntityNews news = new EntityNews();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_News]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, Newsid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return news;
                            }
                            while (OReader.Read())
                            {
                                news.NewsId = (Guid)OReader["NewsId"];
                                news.NewsName = OReader["NewsName"].ToString();
                               // news.NewsDate = CommonFunction.ConvertDate(OReader["NewsDate"].ToString());
                                //news.NewsActiveDate = CommonFunction.ConvertDate(OReader["NewsActiveDate"].ToString());
                                //news.NewsExpairyDate = CommonFunction.ConvertDate(OReader["NewsExpairyDate"].ToString());
                                news.NewsCategory = new EntityNewsCategory();
                                news.NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                news.NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return news;
        }
        public string InsertNewsDetails(EntityNewsDetails NewsDetails)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NewsDetails]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, NewsDetails.NewsId);
                    SQLServer.AddInParameter(dbCommand, "@Languageid", DbType.Guid, NewsDetails.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@NewsHead", DbType.String, NewsDetails.NewsHead);
                    SQLServer.AddInParameter(dbCommand, "@NewsContent", DbType.String, NewsDetails.NewsContent);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateNewsDetails(EntityNewsDetails NewsDetails)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NewsDetails]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@NewsDetailsId", DbType.Guid, NewsDetails.NewsDetailsId);
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, NewsDetails.NewsId);
                    SQLServer.AddInParameter(dbCommand, "@Languageid", DbType.Guid, NewsDetails.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@NewsHead", DbType.String, NewsDetails.NewsHead);
                    SQLServer.AddInParameter(dbCommand, "@NewsContent", DbType.String, NewsDetails.NewsContent);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteNewsDetails(Guid NewsDetailsId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NewsDetails]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@NewsDetailsId", DbType.Guid, NewsDetailsId);
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntityNewsDetails GetNewsDetailsbyLanguageId(Guid NewsId, Guid LanguageId)
        {
            EntityNewsDetails NewsDetails = new EntityNewsDetails();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsDetails]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.SEARCH);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@NewsDetailsId", DbType.Guid, NewsId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return NewsDetails;
                            }
                            while (OReader.Read())
                            {
                                NewsDetails.LanguageId = (Guid)OReader["LanguageId"];
                                NewsDetails.NewsId = (Guid)OReader["NewsId"];
                                NewsDetails.NewsDetailsId = (Guid)OReader["NewsDetailsId"];
                                NewsDetails.NewsHead = OReader["NewsHead"].ToString();
                                NewsDetails.NewsContent = OReader["NewsContent"].ToString();
                                NewsDetails.Language = new EntityLanguage();
                                NewsDetails.Language.LanguageId = (Guid)OReader["LanguageId"];
                                NewsDetails.Language.LanguageName = OReader["LanguageName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return NewsDetails;
        }
        public List<EntityNewsWeb> GetNewsforWebsite()
        {
            List<EntityNewsWeb> lstNews = new List<EntityNewsWeb>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsDetailsforWeb]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return lstNews;
                            }
                            while (OReader.Read())
                            {
                                EntityNewsWeb news = new EntityNewsWeb();
                                news.noofComments = (int)OReader["CommentCount"];
                                news.Image = OReader["Image"].ToString();
                                news.News = new EntityNews();
                                news.News.NewsId = (Guid)OReader["NewsId"];
                                news.News.NewsName = OReader["NewsName"].ToString();
                                news.News.NewsDate = OReader["NewsDate"].ToString();
                                news.News.NewsActiveDate = OReader["NewsActiveDate"].ToString();
                                news.News.NewsExpairyDate = OReader["NewsExpairyDate"].ToString();
                                news.NewsDetails = new EntityNewsDetails();
                                news.NewsDetails.NewsContent = OReader["NewsContent"].ToString();
                                news.NewsDetails.NewsHead = OReader["NewsHead"].ToString();
                                news.NewsCategory = new EntityNewsCategory();
                                news.NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                                news.NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                news.Language = new EntityLanguage();
                                news.Language.LanguageId = (Guid)OReader["LanguageId"];
                                news.Language.LanguageName = OReader["LanguageName"].ToString();
                                lstNews.Add(news);
                                
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return lstNews;
        }
        public List<EntityNewsWeb> GetNewsforWebsite(Guid LanugageId)
        {
            List<EntityNewsWeb> lstNews = new List<EntityNewsWeb>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsDetailsforWebWithLanguage]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@LangugageId", DbType.Guid, LanugageId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return lstNews;
                            }
                            while (OReader.Read())
                            {
                                EntityNewsWeb news = new EntityNewsWeb();
                                news.noofComments = (int)OReader["CommentCount"];
                                news.Image = OReader["Image"].ToString();
                                news.News = new EntityNews();
                                news.News.NewsId = (Guid)OReader["NewsId"];
                                news.News.NewsName = OReader["NewsName"].ToString();
                                news.News.NewsDate = OReader["NewsDate"].ToString();
                                news.News.NewsActiveDate = OReader["NewsActiveDate"].ToString();
                                news.News.NewsExpairyDate = OReader["NewsExpairyDate"].ToString();
                                news.NewsDetails = new EntityNewsDetails();
                                news.NewsDetails.NewsContent = OReader["NewsContent"].ToString();
                                news.NewsDetails.NewsHead = OReader["NewsHead"].ToString();
                                news.NewsCategory = new EntityNewsCategory();
                                news.NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                                news.NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                news.Language = new EntityLanguage();
                                news.Language.LanguageId = (Guid)OReader["LanguageId"];
                                news.Language.LanguageName = OReader["LanguageName"].ToString();
                                lstNews.Add(news);

                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return lstNews;
        }
        public List<EntityNewsWeb> GetNewbyNewsCategory(Guid LanugageId, Guid NewsCategoryId)
        {
            List<EntityNewsWeb> lstNews = new List<EntityNewsWeb>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsDetailsforWebWithLanguage]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@LangugageId", DbType.Guid, LanugageId);
                    SQLServer.AddInParameter(dbCommand, "@NewsCategory", DbType.Guid, NewsCategoryId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return lstNews;
                            }
                            while (OReader.Read())
                            {
                                EntityNewsWeb news = new EntityNewsWeb();
                                news.noofComments = (int)OReader["CommentCount"];
                                news.Image = OReader["Image"].ToString();
                                news.News = new EntityNews();
                                news.News.NewsId = (Guid)OReader["NewsId"];
                                news.News.NewsName = OReader["NewsName"].ToString();
                                news.News.NewsDate = OReader["NewsDate"].ToString();
                                news.News.NewsActiveDate = OReader["NewsActiveDate"].ToString();
                                news.News.NewsExpairyDate = OReader["NewsExpairyDate"].ToString();
                                news.NewsDetails = new EntityNewsDetails();
                                news.NewsDetails.NewsContent = OReader["NewsContent"].ToString();
                                news.NewsDetails.NewsHead = OReader["NewsHead"].ToString();
                                news.NewsCategory = new EntityNewsCategory();
                                news.NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                                news.NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                news.Language = new EntityLanguage();
                                news.Language.LanguageId = (Guid)OReader["LanguageId"];
                                news.Language.LanguageName = OReader["LanguageName"].ToString();
                                lstNews.Add(news);

                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return lstNews;
        }
        public EntityFullNewsInfo GetNewsInfo(Guid NewsId)
        {
            EntityFullNewsInfo Newsinfo = new EntityFullNewsInfo();
            List<EntityLanguage> Languages = new List<EntityLanguage>();
            List<EntityImages> Images = new List<EntityImages>();
            List<EntityComments> Comments = new List<EntityComments>();
            List<EntityNewsDetails> NewsDetailslist = new List<EntityNewsDetails>();
            EntityNews news = new EntityNews();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsInfo]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ADMIN");
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, NewsId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Newsinfo;
                            }
                            while (OReader.Read())
                            {
                                EntityLanguage Language = new EntityLanguage();
                                Language.LanguageId = (Guid)OReader["LanguageId"];
                                Language.LanguageValue = (int)OReader["LanguageValue"];
                                Language.LanguageName = OReader["LanguageName"].ToString();
                                Languages.Add(Language);
                            }
                            Newsinfo.Language = Languages;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityImages Image = new EntityImages();
                                Image.ImageId = (Guid)OReader["ImageId"];
                                Image.ImageMapId = (Guid)OReader["ImageMapId"];
                                Image.ImageName = OReader["ImageName"].ToString();
                                Image.Status = (int)OReader["Status"];
                                Images.Add(Image);
                            }
                            Newsinfo.Images = Images;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityComments Comment = new EntityComments();
                                Comment.CommentId = (Guid)OReader["CommentId"];
                                Comment.CommentMapId = (Guid)OReader["CommentMapId"];
                                Comment.Name = OReader["Name"].ToString();
                                Comment.PhoneNo = OReader["PhoneNo"].ToString();
                                Comment.Comment = OReader["Comment"].ToString();
                                Comment.Status = (int)OReader["Status"];
                                Comment.EmailId = OReader["EmailId"].ToString();
                                Comments.Add(Comment);
                            }
                            Newsinfo.Comments = Comments;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                news.NewsId = (Guid)OReader["NewsId"];
                                news.NewsName = OReader["NewsName"].ToString();
                                //news.NewsDate = CommonFunction.ConvertDate(OReader["NewsDate"].ToString());
                                //news.NewsActiveDate = CommonFunction.ConvertDate(OReader["NewsActiveDate"].ToString());
                                //news.NewsExpairyDate = CommonFunction.ConvertDate(OReader["NewsExpairyDate"].ToString());
                            }
                            Newsinfo.News = news;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityNewsDetails NewsDetails = new EntityNewsDetails();
                                NewsDetails.LanguageId = (Guid)OReader["LanguageId"];
                                NewsDetails.NewsId = (Guid)OReader["NewsId"];
                                NewsDetails.NewsDetailsId = (Guid)OReader["NewsDetailsId"];
                                NewsDetails.NewsHead = OReader["NewsHead"].ToString();
                                NewsDetails.NewsContent = OReader["NewsContent"].ToString();
                                NewsDetails.Language = new EntityLanguage();
                                NewsDetails.Language.LanguageId = (Guid)OReader["LanguageId"];
                                NewsDetails.Language.LanguageName = OReader["LanguageName"].ToString();
                                NewsDetailslist.Add(NewsDetails);
                            }
                            Newsinfo.Detailslist = NewsDetailslist;
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Newsinfo;
        }
        public EntityFullNewsInfo GetNewsInfo(Guid NewsId,Guid LanguageId)
        {
            EntityFullNewsInfo Newsinfo = new EntityFullNewsInfo();
            List<EntityLanguage> Languages = new List<EntityLanguage>();
            List<EntityImages> Images = new List<EntityImages>();
            List<EntityComments> Comments = new List<EntityComments>();
            List<EntityNewsDetails> NewsDetailslist = new List<EntityNewsDetails>();
            EntityNews news = new EntityNews();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsInfo]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "WEBSITE");
                    SQLServer.AddInParameter(dbCommand, "@NewsId", DbType.Guid, NewsId);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, LanguageId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Newsinfo;
                            }
                            while (OReader.Read())
                            {
                                EntityLanguage Language = new EntityLanguage();
                                Language.LanguageId = (Guid)OReader["LanguageId"];
                                Language.LanguageValue = (int)OReader["LanguageValue"];
                                Language.LanguageName = OReader["LanguageName"].ToString();
                                Languages.Add(Language);
                            }
                            Newsinfo.Language = Languages;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityImages Image = new EntityImages();
                                Image.ImageId = (Guid)OReader["ImageId"];
                                Image.ImageMapId = (Guid)OReader["ImageMapId"];
                                Image.ImageName = OReader["ImageName"].ToString();
                                Image.Status = (int)OReader["Status"];
                                Images.Add(Image);
                            }
                            Newsinfo.Images = Images;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityComments Comment = new EntityComments();
                                Comment.CommentId = (Guid)OReader["CommentId"];
                                Comment.CommentMapId = (Guid)OReader["CommentMapId"];
                                Comment.Name = OReader["Name"].ToString();
                                Comment.PhoneNo = OReader["PhoneNo"].ToString();
                                Comment.Status = (int)OReader["Status"];
                                Comment.EmailId = OReader["PhoneNo"].ToString();
                                Comment.Comment = OReader["Comment"].ToString();
                                Comments.Add(Comment);
                            }
                            Newsinfo.Comments = Comments;
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityNews newsdetails = new EntityNews();
                                newsdetails.NewsId = (Guid)OReader["NewsId"];
                                newsdetails.NewsName = OReader["NewsName"].ToString();
                                newsdetails.NewsDate = OReader["NewsDate"].ToString();
                                newsdetails.NewsActiveDate = OReader["NewsActiveDate"].ToString();
                                newsdetails.NewsExpairyDate = OReader["NewsExpairyDate"].ToString();
                                newsdetails.NewsCategory = new EntityNewsCategory();
                                newsdetails.NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                                newsdetails.NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                Newsinfo.News = newsdetails;
                            }
                            OReader.NextResult();
                            while (OReader.Read())
                            {
                                EntityNewsDetails NewsDetails = new EntityNewsDetails();
                                NewsDetails.LanguageId = (Guid)OReader["LanguageId"];
                                NewsDetails.NewsId = (Guid)OReader["NewsId"];
                                NewsDetails.NewsDetailsId = (Guid)OReader["NewsDetailsId"];
                                NewsDetails.NewsHead = OReader["NewsHead"].ToString();
                                NewsDetails.NewsContent = OReader["NewsContent"].ToString();
                                NewsDetails.Language = new EntityLanguage();
                                NewsDetails.Language.LanguageId = (Guid)OReader["LanguageId"];
                                NewsDetails.Language.LanguageName = OReader["LanguageName"].ToString();
                                Newsinfo.NewsDetails = NewsDetails;
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Newsinfo;
        }
        public string ChangeCommentStatus(Guid CommentId, int Status)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Comments_ChangeStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@CommentID", DbType.Guid, CommentId);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, Status);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return IResult;
        }
        #endregion
        #region NewsCategory
        public List<EntityNewsCategory> GetNewsCategories()
        {
            List<EntityNewsCategory> NewsCategories = new List<EntityNewsCategory>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return NewsCategories;
                            }
                            while (OReader.Read())
                            {
                                EntityNewsCategory Category = new EntityNewsCategory();
                                Category.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                Category.NewsCategory = OReader["NewsCategory"].ToString();
                                NewsCategories.Add(Category);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return NewsCategories;
        }
        public EntityNewsCategory GetNewsCategory(Guid NewsCategoryId)
        {
            EntityNewsCategory NewsCategory = new EntityNewsCategory();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, NewsCategoryId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return NewsCategory;
                            }
                            while (OReader.Read())
                            {
                                NewsCategory.NewsCategoryId = (Guid)OReader["NewsCategoryId"];
                                NewsCategory.NewsCategory = OReader["NewsCategory"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return NewsCategory;
        }
        public string InsertNewsCategory(EntityNewsCategory NewsCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NewCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, NewsCategory.NewsCategory);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateNewsCategory(EntityNewsCategory NewsCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NewCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, NewsCategory.NewsCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, NewsCategory.NewsCategory);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteNewsCategory(Guid NewsCategoryid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NewCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, NewsCategoryid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Events Category
        public string InsertEventCategory(EntityEventCategory EventCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_EventCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, EventCategory.EventCategoryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateEventCategory(EntityEventCategory EventCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_EventCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, EventCategory.EventCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, EventCategory.EventCategoryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteEventCategory(Guid EventCategoryid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_EventCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, EventCategoryid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Video Category
        public string InsertVideoCategory(EntityVideoCategory VideoCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_VideoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, VideoCategory.VideoCategoryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateVideoCategory(EntityVideoCategory VideoCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_VideoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, VideoCategory.VideoCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, VideoCategory.VideoCategoryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteVideoCategory(Guid VideoCategoryid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_VideoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, VideoCategoryid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Photo Category
        public string InsertPhotoCategory(EntityPhotoCategory PhotoCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PhotoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, PhotoCategory.PhotoCategoryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdatePhotoCategory(EntityPhotoCategory PhotoCategory)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PhotoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, PhotoCategory.PhotoCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@CategoryName", DbType.String, PhotoCategory.PhotoCategoryName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeletePhotoCategory(Guid PhotoCategoryid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PhotoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, PhotoCategoryid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region photo
        public string InsertPhoto(EntityPhoto Photo)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_photo]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@photoid", DbType.Guid, Photo.PhotoId);
                    SQLServer.AddInParameter(dbCommand, "@photoCategory", DbType.Guid, Photo.PhotoCategory.PhotoCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@photoUrl", DbType.String, Photo.PhotoUrl);
                    SQLServer.AddInParameter(dbCommand, "@status", DbType.Int32, Photo.status);
                    SQLServer.AddInParameter(dbCommand, "@photoName", DbType.String, Photo.PhotoName);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@on", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdatePhoto(EntityPhoto Photo)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_photo]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@photoid", DbType.Guid, Photo.PhotoId);
                    SQLServer.AddInParameter(dbCommand, "@photoCategory", DbType.Guid, Photo.PhotoCategory.PhotoCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@photoUrl", DbType.String, Photo.PhotoUrl);
                    SQLServer.AddInParameter(dbCommand, "@status", DbType.Int32, Photo.status);
                    SQLServer.AddInParameter(dbCommand, "@photoName", DbType.String, Photo.PhotoName);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@on", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeletePhoto(Guid Photoid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_photo]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@photoid", DbType.Guid, Photoid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Events
        public string InsertEvents(EntityEvents Events)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Events]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@EventId", DbType.Guid, Events.EventId);
                    SQLServer.AddInParameter(dbCommand, "@EventHead", DbType.String, Events.EventHead);
                    SQLServer.AddInParameter(dbCommand, "@EventContent", DbType.String, Events.EventContent);
                    SQLServer.AddInParameter(dbCommand, "@ActiveDate", DbType.DateTime, Events.ActiveDate);
                    SQLServer.AddInParameter(dbCommand, "@ExpairyDate", DbType.DateTime, Events.ExpairyDate);
                    SQLServer.AddInParameter(dbCommand, "@CreatedBy", DbType.Guid, Events.Createdby);
                    SQLServer.AddInParameter(dbCommand, "@CreatedOn", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@Image", DbType.String, Events.Image);
                    SQLServer.AddInParameter(dbCommand, "@SharingType", DbType.Int32, Events.SharingType);
                    SQLServer.AddInParameter(dbCommand, "@EventCatgory", DbType.Guid, Events.EventCategory.EventCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@EventEndTime", DbType.String, Events.EventEndTime);
                    SQLServer.AddInParameter(dbCommand, "@EventStartTime", DbType.String, Events.EventStartTime);
                    SQLServer.AddInParameter(dbCommand, "@EventPlace", DbType.String, Events.EventPlace);
                    SQLServer.AddInParameter(dbCommand, "@EventStartDate", DbType.DateTime, Events.EventStartDate);
                    SQLServer.AddInParameter(dbCommand, "@EventEndDate", DbType.DateTime, Events.EventEndDate);
                    SQLServer.AddInParameter(dbCommand, "@Userid", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@IsSuperUser", DbType.Boolean, Current.IsSuperAdmin);
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;

        }
        public string UpdateEvents(EntityEvents Events)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Events]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@EventId", DbType.Guid, Events.EventId);
                    SQLServer.AddInParameter(dbCommand, "@EventHead", DbType.String, Events.EventHead);
                    SQLServer.AddInParameter(dbCommand, "@EventContent", DbType.String, Events.EventContent);
                    SQLServer.AddInParameter(dbCommand, "@ActiveDate", DbType.DateTime, Events.ActiveDate);
                    SQLServer.AddInParameter(dbCommand, "@ExpairyDate", DbType.DateTime, Events.ExpairyDate);
                    SQLServer.AddInParameter(dbCommand, "@CreatedBy", DbType.Guid, Events.Createdby);
                    SQLServer.AddInParameter(dbCommand, "@CreatedOn", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@Image", DbType.String, Events.Image);
                    SQLServer.AddInParameter(dbCommand, "@SharingType", DbType.Int32, Events.SharingType);
                    SQLServer.AddInParameter(dbCommand, "@EventCatgory", DbType.Guid, Events.EventCategory.EventCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@EventEndTime", DbType.String, Events.EventEndTime);
                    SQLServer.AddInParameter(dbCommand, "@EventStartTime", DbType.String, Events.EventStartTime);
                    SQLServer.AddInParameter(dbCommand, "@EventPlace", DbType.String, Events.EventPlace);
                    SQLServer.AddInParameter(dbCommand, "@EventStartDate", DbType.DateTime, Events.EventStartDate);
                    SQLServer.AddInParameter(dbCommand, "@EventEndDate", DbType.DateTime, Events.EventEndDate);
                    SQLServer.AddInParameter(dbCommand, "@Userid", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@IsSuperUser", DbType.Boolean, Current.IsSuperAdmin);
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;

        }
        public string DeleteEvents(Guid EventId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Events]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@EventId", DbType.Guid, EventId);
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;

        }
        public EntityEvents GetEvent(Guid EventId)
        {
            EntityEvents Event = new EntityEvents();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Events]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@Eventid", DbType.Guid, EventId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Event;
                            }
                            while (OReader.Read())
                            {
                                
                                Event.EventId = (Guid)OReader["EventId"];
                                Event.EventHead = OReader["EventHead"].ToString();
                                Event.EventContent = OReader["EventContent"].ToString();
                                Event.ActiveDate = (DateTime)OReader["ActiveDate"];
                                Event.Createdby = (Guid)OReader["Createdby"];
                                Event.CreatedOn = (DateTime)OReader["CreatedOn"];
                                Event.EventCategoryId = (Guid)OReader["EventCategoryId"];
                                Event.EventEndDate = (DateTime)OReader["EventEndDate"];
                                Event.EventEndTime = OReader["EventEndTime"].ToString();
                                Event.EventPlace = OReader["EventPlace"].ToString();
                                Event.EventStartDate = (DateTime)OReader["EventStartDate"];
                                Event.EventStartTime = OReader["EventStartTime"].ToString();
                                Event.ExpairyDate = (DateTime)OReader["ExpairyDate"];
                                Event.Image = OReader["Image"].ToString();
                                Event.SharingType = (int)OReader["SharingType"];
                                Event.Status = new EnitityStatus();
                                Event.Status.StatusId = (int)OReader["StatusId"];
                                Event.Status.StatusTypeName = OReader["StatusTypeName"].ToString();
                                Event.EventCategory = new EntityEventCategory();
                                Event.EventCategory.EventCategoryId = (Guid)OReader["EventCategoryId"];
                                Event.EventCategory.EventCategoryName = OReader["EventCategoryName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Event;
        }
        public List<EntityEvents> GetEvents()
        {
            List<EntityEvents> Events = new List<EntityEvents>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Events]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Events;
                            }
                            while (OReader.Read())
                            {
                                EntityEvents Event = new EntityEvents();
                                Event.EventId = (Guid)OReader["EventId"];
                                Event.EventHead = OReader["EventHead"].ToString();
                                Event.EventContent = OReader["EventContent"].ToString();
                                Event.ActiveDate = (DateTime)OReader["ActiveDate"];
                                Event.Createdby = (Guid)OReader["Createdby"];
                                Event.CreatedOn = (DateTime)OReader["CreatedOn"];
                                Event.EventCategoryId = (Guid)OReader["EventCategoryId"];
                                Event.EventEndDate = (DateTime)OReader["EventEndDate"];
                                Event.EventEndTime = OReader["EventEndTime"].ToString();
                                Event.EventPlace = OReader["EventPlace"].ToString();
                                Event.EventStartDate = (DateTime)OReader["EventStartDate"];
                                Event.EventStartTime = OReader["EventStartTime"].ToString();
                                Event.ExpairyDate = (DateTime)OReader["ExpairyDate"];
                                Event.Image = OReader["Image"].ToString();
                                Event.SharingType = (int)OReader["SharingType"];
                                Event.Status = new EnitityStatus();
                                Event.Status.StatusId = (int)OReader["StatusId"];
                                Event.Status.StatusTypeName = OReader["StatusTypeName"].ToString();
                                Event.EventCategory = new EntityEventCategory();
                                Event.EventCategory.EventCategoryId = (Guid)OReader["EventCategoryId"];
                                Event.EventCategory.EventCategoryName = OReader["EventCategoryName"].ToString();
                                Events.Add(Event);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Events;
        }
        public string InsertEventTable(EntityEventTable EventTable)
        {
            return "";
        }
        #endregion
        #region Subcriber
        public string InsertSubcriber(EntitySubcriber Subcriber)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Subcriber]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberName", DbType.String, Subcriber.SubcriberName);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberPhone", DbType.String, Subcriber.SubcriberPhone);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberEmail", DbType.String, Subcriber.SubcriberEmail);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberStatus", DbType.Int32, 1);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateSubcriber(EntitySubcriber Subcriber)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Subcriber]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberId", DbType.String, Subcriber.SubcriberId);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberName", DbType.String, Subcriber.SubcriberName);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberPhone", DbType.String, Subcriber.SubcriberPhone);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberEmail", DbType.String, Subcriber.SubcriberEmail);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberStatus", DbType.Int32, 1);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteSubcriber(Guid SubcriberId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Subcriber]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberId", DbType.String, SubcriberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntitySubcriber GetSubcriber(Guid SubcriberId)
        {
            EntitySubcriber Subcriber = new EntitySubcriber();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Subcriber]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@SubcriberId", DbType.Guid, SubcriberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Subcriber;
                            }
                            while (OReader.Read())
                            {

                                Subcriber.SubcriberId = (Guid)OReader["SubcriberId"];
                                Subcriber.SubcriberName = OReader["SubcriberName"].ToString();
                                Subcriber.Status = OReader["EventContent"].ToString();
                                Subcriber.SubcriberOn = OReader["SubcriberOn"].ToString();
                                Subcriber.SubcriberPhone = OReader["SubcriberPhone"].ToString();
                                Subcriber.SubcriberEmail = OReader["SubcriberEmail"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Subcriber;
        }
        public List<EntitySubcriber> GetSubcribers()
        {
            List<EntitySubcriber> Subcribers = new List<EntitySubcriber>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Subcriber]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Subcribers;
                            }
                            while (OReader.Read())
                            {
                                EntitySubcriber Subcriber = new EntitySubcriber();
                                Subcriber.SubcriberId = (Guid)OReader["SubcriberId"];
                                Subcriber.SubcriberName = OReader["SubcriberName"].ToString();
                                Subcriber.Status = OReader["Status"].ToString();
                                Subcriber.SubcriberOn = OReader["SubcriberOn"].ToString();
                                Subcriber.SubcriberPhone = OReader["SubcriberPhone"].ToString();
                                Subcriber.SubcriberEmail = OReader["SubcriberEmail"].ToString();
                                Subcribers.Add(Subcriber);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Subcribers;
        }
        #endregion
        #region VideoCategory
        public List<EntityVideoCategory> GetVideoCategory()
        {
            List<EntityVideoCategory> VideoCategories = new List<EntityVideoCategory>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_VideoCategory]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return VideoCategories;
                            }
                            while (OReader.Read())
                            {
                                EntityVideoCategory videoCategory = new EntityVideoCategory();
                                videoCategory.VideoCategoryName = OReader["VideoCategoryName"].ToString();
                                videoCategory.VideoCategoryId = (Guid)OReader["VideoCategoryId"];
                                VideoCategories.Add(videoCategory);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return VideoCategories;
        }
        public EntityVideoCategory GetVideoCategory(Guid VideoCategoryId)
        {
            EntityVideoCategory videoCategory = new EntityVideoCategory();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_VideoCategory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@Categoryid", DbType.Guid, VideoCategoryId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return videoCategory;
                            }
                            while (OReader.Read())
                            {
                                videoCategory.VideoCategoryName = OReader["VideoCategoryName"].ToString();
                                videoCategory.VideoCategoryId = (Guid)OReader["VideoCategoryId"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return videoCategory;
        }
        public string InsertVideo(EntityVideos Videos)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Videos]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@VideoCategoryid", DbType.Guid, Videos.VideoCategory.VideoCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@VideoTitle", DbType.String, Videos.VideoTitle);
                    SQLServer.AddInParameter(dbCommand, "@VideoUrl", DbType.String, Videos.VideoUrl);
                    SQLServer.AddInParameter(dbCommand, "@VideoDate", DbType.String, Videos.VideoDate);
                    SQLServer.AddInParameter(dbCommand, "@VideoDesc", DbType.String, Videos.VideoDesc);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateVideo(EntityVideos Videos)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Videos]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@Videoid", DbType.Guid, Videos.VideoId);
                    SQLServer.AddInParameter(dbCommand, "@VideoCategoryid", DbType.Guid, Videos.VideoCategory.VideoCategoryId);
                    SQLServer.AddInParameter(dbCommand, "@VideoTitle", DbType.String, Videos.VideoTitle);
                    SQLServer.AddInParameter(dbCommand, "@VideoUrl", DbType.String, Videos.VideoUrl);
                    SQLServer.AddInParameter(dbCommand, "@VideoDate", DbType.String, Videos.VideoDate);
                    SQLServer.AddInParameter(dbCommand, "@VideoDesc", DbType.String, Videos.VideoDesc);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteVideo(string videoid)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Videos]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    if (!string.IsNullOrEmpty(videoid))
                    {
                        SQLServer.AddInParameter(dbCommand, "@Videoid", DbType.String, videoid);
                    }
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public EntityVideos GetVideo(Guid videoid)
        {
            EntityVideos Video = new EntityVideos();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Videos]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {

                        SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                        SQLServer.AddInParameter(dbCommand, "@VideoId", DbType.Guid, videoid);
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Video;
                            }
                            while (OReader.Read())
                            {
                                Video.VideoId = (Guid)OReader["VideoId"];
                                Video.VideoTitle = OReader["VideoTitle"].ToString();
                                Video.VideoUrl = OReader["VideoUrl"].ToString();
                                //Video.VideoDate = CommonFunction.ConvertDate(OReader["VideoDate"].ToString());
                                //Video.VideoDesc = OReader["VideoDesc"].ToString();
                                Video.VideoCategory = new EntityVideoCategory();
                                Video.VideoCategory.VideoCategoryId = (Guid)OReader["VideoCategoryId"];
                                Video.VideoCategory.VideoCategoryName = OReader["VideoCategoryName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Video;
        }
        public List<EntityVideos> GetVideo()
        {
            List<EntityVideos> Videos = new List<EntityVideos>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Videos]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {

                        SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Videos;
                            }
                            while (OReader.Read())
                            {
                                EntityVideos Video = new EntityVideos();
                                Video.VideoId = (Guid)OReader["VideoId"];
                                Video.VideoTitle = OReader["VideoTitle"].ToString();
                                Video.VideoUrl = OReader["VideoUrl"].ToString();
                               // Video.VideoDate = CommonFunction.ConvertDate(OReader["VideoDate"].ToString());
                                Video.VideoDesc = OReader["VideoDesc"].ToString();
                                Video.VideoCategory = new EntityVideoCategory();
                                Video.VideoCategory.VideoCategoryId = (Guid)OReader["VideoCategoryId"];
                                Video.VideoCategory.VideoCategoryName = OReader["VideoCategoryName"].ToString();
                                Videos.Add(Video);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Videos;
        }
        public List<EntityVideos> GetVideoByCategory(Guid VideoCategoryId)
        {
            List<EntityVideos> Videos = new List<EntityVideos>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Videos]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {

                        SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.SEARCH);
                        SQLServer.AddInParameter(dbCommand, "@VideoCategory", DbType.Guid, VideoCategoryId);
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Videos;
                            }
                            while (OReader.Read())
                            {
                                EntityVideos Video = new EntityVideos();
                                Video.VideoId = (Guid)OReader["VideoId"];
                                Video.VideoTitle = OReader["VideoTitle"].ToString();
                                Video.VideoUrl = OReader["VideoUrl"].ToString();
                                //Video.VideoDate = CommonFunction.ConvertDate(OReader["VideoDate"].ToString());
                                Video.VideoDesc = OReader["VideoDesc"].ToString();
                                Video.VideoCategory = new EntityVideoCategory();
                                Video.VideoCategory.VideoCategoryId = (Guid)OReader["VideoCategoryId"];
                                Video.VideoCategory.VideoCategoryName = OReader["VideoCategoryName"].ToString();
                                Videos.Add(Video);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Videos;
        }
        
        #endregion
        #region  Message
        public string InsertMessage(EntityMessage Message)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Message]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, Message.Name);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Message.Email);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, Message.Phone);
                    SQLServer.AddInParameter(dbCommand, "@Message", DbType.String, Message.Message);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityMessage> GetMessage()
        {
            List<EntityMessage> Messages = new List<EntityMessage>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Message]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Messages;
                            }
                            while (OReader.Read())
                            {
                                EntityMessage Message = new EntityMessage();
                                Message.ContactId = (Guid)OReader["ContactId"];
                                Message.Email = OReader["Email"].ToString();
                                Message.Message = OReader["Message"].ToString();
                                Message.Name = OReader["Name"].ToString();
                                Message.Phone = OReader["Phone"].ToString();
                                Message.Status = (int)OReader["Status"];
                                Messages.Add(Message);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Messages;
        }
        #endregion
        #region Banner
        public string InsertBanner(EntityBanner Banner)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Banner]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@BannerImage", DbType.String, Banner.BannerImage);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Boolean, Banner.Status);
                    SQLServer.AddInParameter(dbCommand, "@OrderNo", DbType.Int32, Banner.OrderNo);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateBanner(EntityBanner Banner)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Banner]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@BannerId", DbType.Guid, Banner.BannerId);
                    SQLServer.AddInParameter(dbCommand, "@BannerImage", DbType.String, Banner.BannerImage);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Boolean, Banner.Status);
                    SQLServer.AddInParameter(dbCommand, "@OrderNo", DbType.Int32, Banner.OrderNo);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteBanner(Guid BannerId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Banner]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@BannerId", DbType.Guid, BannerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityBanner> GetBanner()
        {
            List<EntityBanner> Banners = new List<EntityBanner>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Banner]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Banners;
                            }
                            while (OReader.Read())
                            {
                                EntityBanner Banner = new EntityBanner();
                                Banner.BannerId = (Guid)OReader["BannerId"];
                                Banner.BannerImage = OReader["BannerImage"].ToString();
                                Banner.OrderNo = (Int32)OReader["OrderNo"];
                                Banner.Status = (bool)OReader["Status"];
                                Banners.Add(Banner);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Banners;
        }
        public EntityBanner GetBanner(Guid BannerId)
        {
            EntityBanner Banner = new EntityBanner();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Banner]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@BannerId", DbType.Guid, BannerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Banner;
                            }
                            while (OReader.Read())
                            {
                                Banner.BannerId = (Guid)OReader["BannerId"];
                                Banner.BannerImage = OReader["BannerImage"].ToString();
                                Banner.OrderNo = (Int32)OReader["OrderNo"];
                                Banner.Status = (bool)OReader["Status"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Banner;
        }
        #endregion
        #region Article
        public string InsertArticle(EntityArticle Article)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Article]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ArticleTitle", DbType.String, Article.ArticleTitle);
                    SQLServer.AddInParameter(dbCommand, "@ArticleName", DbType.String, Article.ArticleName);
                    SQLServer.AddInParameter(dbCommand, "@ArticleImage", DbType.String, Article.ArticleImage);
                    SQLServer.AddInParameter(dbCommand, "@ArticleContent", DbType.String, Article.ArticleContent);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateArticle(EntityArticle Article)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Article]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ArtitcleId", DbType.Guid, Article.ArtitcleId);
                    SQLServer.AddInParameter(dbCommand, "@ArticleName", DbType.String, Article.ArticleName);
                    SQLServer.AddInParameter(dbCommand, "@ArticleTitle", DbType.String, Article.ArticleTitle);
                    SQLServer.AddInParameter(dbCommand, "@ArticleImage", DbType.String, Article.ArticleImage);
                    SQLServer.AddInParameter(dbCommand, "@ArticleContent", DbType.String, Article.ArticleContent);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteArticle(Guid ArticleId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Article]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ArtitcleId", DbType.Guid, ArticleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityArticle> GetArticle()
        {
            List<EntityArticle> Articles = new List<EntityArticle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Article]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Articles;
                            }
                            while (OReader.Read())
                            {
                                EntityArticle Article = new EntityArticle();
                                Article.ArtitcleId = (Guid)OReader["ArtitcleId"];
                                Article.ArticleTitle = OReader["ArticleTitle"].ToString();
                                Article.ArticleImage = OReader["ArticleImage"].ToString();
                                Article.ArticleContent = OReader["ArticleContent"].ToString();
                                Article.ArticleName = OReader["ArticleName"].ToString();
                                Articles.Add(Article);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Articles;
        }
        public EntityArticle GetArticle(Guid ArticleId)
        {
            EntityArticle Article = new EntityArticle();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Article]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ArticleId", DbType.Guid, ArticleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Article;
                            }
                            while (OReader.Read())
                            {
                                Article.ArticleName = OReader["ArticleName"].ToString();
                                Article.ArtitcleId = (Guid)OReader["ArtitcleId"];
                                Article.ArticleTitle = OReader["ArticleTitle"].ToString();
                                Article.ArticleImage = OReader["ArticleImage"].ToString();
                                Article.ArticleContent = OReader["ArticleContent"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Article;
        }
        #endregion
        #region Newsletter
        public string InsertNewsletter(EntityNewsletter Newsletter)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Newsletter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterId", DbType.Guid, Newsletter.NewsLetterId);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterSubject", DbType.String, Newsletter.NewsLetterSubject);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterContent", DbType.String, Newsletter.NewsLetterContent);
                    if (Current.IsSuperAdmin == true)
                    {
                        SQLServer.AddInParameter(dbCommand, "@NewsLetterStatus", DbType.Int32, 3);
                    }
                    else
                    {
                        SQLServer.AddInParameter(dbCommand, "@NewsLetterStatus", DbType.Int32, 1);
                    }
                    SQLServer.AddInParameter(dbCommand, "@IsDeleted", DbType.Boolean, 0);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection()) 
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateNewsletter(EntityNewsletter Newsletter)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Newsletter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterId", DbType.Guid, Newsletter.NewsLetterId);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterSubject", DbType.String, Newsletter.NewsLetterSubject);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterContent", DbType.String, Newsletter.NewsLetterContent);
                    if (Current.IsSuperAdmin == true)
                    {
                        SQLServer.AddInParameter(dbCommand, "@NewsLetterStatus", DbType.Int32, 3);
                    }
                    else
                    {
                        SQLServer.AddInParameter(dbCommand, "@NewsLetterStatus", DbType.Int32, 1);
                    }
                    SQLServer.AddInParameter(dbCommand, "@IsDeleted", DbType.Boolean, 0);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteNewsletter(Guid NewsletterId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Newsletter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterId", DbType.Guid, NewsletterId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityNewsletter> GetNewsletters()
        {
            List<EntityNewsletter> Newsletters = new List<EntityNewsletter>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsLetter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Newsletters;
                            }
                            while (OReader.Read())
                            {
                                EntityNewsletter Newsletter = new EntityNewsletter();
                                Newsletter.NewsLetterId = (Guid)OReader["NewsLetterId"];
                                Newsletter.NewsLetterSubject = OReader["NewsLetterSubject"].ToString();
                                Newsletter.NewsLetterContent = OReader["NewsLetterContent"].ToString();
                                Newsletter.CreatedOn = (DateTime)OReader["CreatedOn"];
                                Newsletter.IsDeleted = (bool)OReader["IsDeleted"];
                                Newsletter.NewsLetterStatus = (int)OReader["NewsLetterStatus"];
                                Newsletters.Add(Newsletter);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Newsletters;
        }
        public EntityNewsletter GetNewsletter(Guid NewsletterId)
        {
            EntityNewsletter Newsletter = new EntityNewsletter();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NewsLetter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterId", DbType.Guid, NewsletterId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Newsletter;
                            }
                            while (OReader.Read())
                            {
                                Newsletter.NewsLetterId = (Guid)OReader["NewsLetterId"];
                                Newsletter.NewsLetterSubject = OReader["NewsLetterSubject"].ToString();
                                Newsletter.NewsLetterContent = OReader["NewsLetterContent"].ToString();
                                Newsletter.CreatedOn = (DateTime)OReader["CreatedOn"];
                                Newsletter.IsDeleted = (bool)OReader["IsDeleted"];
                                Newsletter.NewsLetterStatus = (int)OReader["NewsLetterStatus"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Newsletter;
        }
        public string ApproveRejectNewsletter(Guid NewsletterId, int Status)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Newsletter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "APPROVE");
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterId", DbType.Guid, NewsletterId);
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterStatus", DbType.String, Status);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string SendNewsletter(Guid NewsletterId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Newsletter]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "SEND");
                    SQLServer.AddInParameter(dbCommand, "@NewsLetterId", DbType.Guid, NewsletterId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region Album
        public string InsertAlbum(EntityAlbums Album)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Albums]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@AlbumName", DbType.String, Album.AlbumName);
                    SQLServer.AddInParameter(dbCommand, "@AlbumtitleImage", DbType.String, CommonFunction.UploadImage("~/Uploads/Album/AlbumTitleImage", Album.AlbumtitleImage));
                    SQLServer.AddInParameter(dbCommand, "@AlbumBy", DbType.String, Album.AlbumBy);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateAlbum(EntityAlbums Album)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Albums]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@AlbumId", DbType.Guid, Album.AlbumId);
                    SQLServer.AddInParameter(dbCommand, "@AlbumName", DbType.String, Album.AlbumName);
                    SQLServer.AddInParameter(dbCommand, "@AlbumtitleImage", DbType.String, CommonFunction.UploadImage("~/Uploads/Album/AlbumTitleImage", Album.AlbumtitleImage));
                    SQLServer.AddInParameter(dbCommand, "@AlbumBy", DbType.String, Album.AlbumBy);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteAlbum(Guid AlbumId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Albums]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@AlbumId", DbType.Guid, AlbumId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityAlbums> GetAlbums(int PageNo, string SearchKey)
        {
            List<EntityAlbums> Albums = new List<EntityAlbums>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Albums]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Albums;
                            }
                            while (OReader.Read())
                            {
                                EntityAlbums Album = new EntityAlbums();
                                Album.AlbumId = (Guid)OReader["AlbumId"];
                                Album.AlbumName = OReader["AlbumName"].ToString();
                                Album.AlbumtitleImage = OReader["AlbumtitleImage"].ToString();
                                Album.AlbumBy = OReader["AlbumBy"].ToString();
                                Album.TotalCounts = (Int32)OReader["TotalCounts"];
                                
                                Albums.Add(Album);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Albums;
        }
        public EntityAlbums GetAlbum(Guid AlbumId)
        {
            EntityAlbums Album = new EntityAlbums();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Albums]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@AlbumId", DbType.Guid, AlbumId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Album;
                            }
                            while (OReader.Read())
                            {
                                Album.AlbumId = (Guid)OReader["AlbumId"];
                                Album.AlbumName = OReader["AlbumName"].ToString();
                                Album.AlbumtitleImage = OReader["AlbumtitleImage"].ToString();
                                Album.AlbumBy = OReader["AlbumBy"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Album;
        }
        #endregion
        #region AlbumSongs
        public string InsertSong(EntitySongs Song)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Songs]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@AlbumId", DbType.Guid, Song.AlbumId);
                    SQLServer.AddInParameter(dbCommand, "@SongName", DbType.String, Song.SongName);
                    SQLServer.AddInParameter(dbCommand, "@SongBy", DbType.String, Song.SongBy);
                    SQLServer.AddInParameter(dbCommand, "@SongFileName", DbType.String,Song.SongFileName);
                    SQLServer.AddInParameter(dbCommand, "@SongTitleImage", DbType.String, CommonFunction.UploadImage("~/Uploads/Album/SongTitleImage", Song.SongTitleImage));
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateSong(EntitySongs Song)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Songs]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@SongId", DbType.Guid, Song.SongId);
                    SQLServer.AddInParameter(dbCommand, "@AlbumId", DbType.Guid, Song.AlbumId);
                    SQLServer.AddInParameter(dbCommand, "@SongName", DbType.String, Song.SongName);
                    SQLServer.AddInParameter(dbCommand, "@SongBy", DbType.String, Song.SongBy);
                    SQLServer.AddInParameter(dbCommand, "@SongFileName", DbType.String, Song.SongFileName);
                    SQLServer.AddInParameter(dbCommand, "@SongTitleImage", DbType.String, CommonFunction.UploadImage("~/Uploads/Album/SongTitleImage", Song.SongTitleImage));
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteSong(Guid SongId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Songs]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@SongId", DbType.Guid, SongId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntitySongs> GetSongs()
        {
            List<EntitySongs> Songs = new List<EntitySongs>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Songs]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Songs;
                            }
                            while (OReader.Read())
                            {
                                EntitySongs Song = new EntitySongs();
                                Song.SongId = (Guid)OReader["SongId"];
                                Song.AlbumId = (Guid)OReader["AlbumId"];
                                Song.SongName = OReader["SongName"].ToString();
                                Song.SongBy = OReader["SongBy"].ToString();
                                Song.SongFileName = OReader["SongFileName"].ToString();
                                Song.SongTitleImage = OReader["SongTitleImage"].ToString();
                                Song.Albums = GetAlbum(Song.AlbumId);
                                Songs.Add(Song);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Songs;
        }
        public EntitySongs GetSongs(Guid SongsId)
        {
            EntitySongs Song = new EntitySongs();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Songs]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@SongId", DbType.Guid, SongsId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Song;
                            }
                            while (OReader.Read())
                            {
                                Song.SongId = (Guid)OReader["SongId"];
                                Song.AlbumId = (Guid)OReader["AlbumId"];
                                Song.SongName = OReader["SongName"].ToString();
                                Song.SongBy = OReader["SongBy"].ToString();
                                Song.SongFileName = OReader["SongFileName"].ToString();
                                Song.SongTitleImage = OReader["SongTitleImage"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Song;
        }
        public List<EntitySongs> GetSongsByAlbums(Guid AlbumId)
        {
            List<EntitySongs> Songs = new List<EntitySongs>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Songs]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ALBUM");
                    SQLServer.AddInParameter(dbCommand, "@AlbumId", DbType.Guid, AlbumId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Songs;
                            }
                            while (OReader.Read())
                            {
                                EntitySongs Song = new EntitySongs();
                                Song.SongId = (Guid)OReader["SongId"];
                                Song.AlbumId = (Guid)OReader["AlbumId"];
                                Song.SongName = OReader["SongName"].ToString();
                                Song.SongBy = OReader["SongBy"].ToString();
                                Song.SongFileName = OReader["SongFileName"].ToString();
                                Song.SongTitleImage = OReader["SongTitleImage"].ToString();
                                Songs.Add(Song);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Songs;
        }
        #endregion
    }
}

